TOP

SQL BETWEEN 语句

YouLibreCalc for Excel logo

SQL BETWEEN 描述

BETWEEN 运算符选择给定范围内的值。值可以是数字、文本或日期。

BETWEEN 运算符选择包括开始值和结束值的数据。


BETWEEN 语法

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2

演示数据库

以下是“Products”表中的示例:

ProductIDProductNameSupplierIDCategoryIDUnitPrice
1Chais1110 boxes x 20 bags18.00
2Chang1124 - 12 oz bottles19.00
3Aniseed Syrup1212 - 550 ml bottles10.00
4Chef Anton's Cajun Seasoning2248 - 6 oz jars22.00
5Chef Anton's Gumbo Mix2236 boxes21.35

SQL BETWEEN 示例

以下 SQL 语句选择价格在 10 到 20 之间的所有产品:

Run SQLSELECT * FROM Products 
WHERE Price BETWEEN 10 AND 20

NOT BETWEEN 示例

要显示上一个示例范围之外的产品,请使用 NOT BETWEEN:

Run SQLSELECT * FROM Products 
WHERE Price NOT BETWEEN 10 AND 20

BETWEEN 和 IN 示例

以下 SQL 语句选择价格在 10 到 20 之间的所有产品,同时不显示 CategoryID 1、2 或 3 的产品:

Run SQLSELECT * FROM Products 
WHERE Price BETWEEN 10 AND 20 
AND CategoryID NOT IN (1,2,3)

BETWEEN 带有文本值示例

以下 SQL 语句从 ProductName 中选择“Carnarvon Tigers”和“Mozzarella di Giovanni”之间的所有产品:

Run SQLSELECT * FROM Products 
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' 
ORDER BY ProductName

以下 SQL 语句从 ProductName 中选择“Carnarvon Tigers”和“Chef Anton's Cajun Seasoning”之间的所有产品:

Run SQLSELECT * FROM Products 
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Chartreuse verte' 
ORDER BY ProductName

NOT BETWEEN 带有文本值示例

以下 SQL 语句从 ProductName 选择所有产品,而不是在“Carnarvon Tigers”和“Mozzarella di Giovanni”之间:

Run SQLSELECT * FROM Products 
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' 
ORDER BY ProductName

表样

以下是表“Orders”(“订单”)中的示例:

ProductIDOrderIDCustomerIDEmployeeIDOrderDateShipperID
1102489051996-07-043
2102498161996-07-051
3102503441996-07-082
4102518431996-07-081
5102527641996-07-092

BETWEEN 与日期示例

以下 SQL 语句从 OrderDate 中选择“01-July-1996”和“31-July-1996”之间的所有订单 (Orders):

SELECT * FROM Orders 
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#

或者:

Run SQLSELECT * FROM Orders 
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'